# 井下传感器安装位置.xlsx 表格方法


import openpyxl

class XlsxMethods:

    def __enter__(self):
        return self
    

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.workbook.close()


    def __init__(self, file_path="井下传感器安装位置.xlsx"):

        self.file_path = file_path

        self.workbook = openpyxl.load_workbook(self.file_path)

        self.sheet = self.workbook.active

        # print(self.sheet.title)
        # print(dir(self.sheet))


    def get_master_station_mac(self):

        return self.sheet['D3'].value
    
    def get_group_data_by_id(self, group_id):

        result = {
            "group_id": group_id,
            "bracket_number": None,
            "station_mac": None,
            "sensor_list": []
        }

        row_num = group_id * 5

        result["bracket_number"] = self.sheet.cell(row=row_num, column=2).value
        result["station_mac"] = self.sheet.cell(row=row_num, column=3).value

        for i in range(5):

            result["sensor_list"].append({
                "sensor_type": self.sheet.cell(row=row_num+i, column=4).value,
                "sensor_mac": self.sheet.cell(row=row_num+i, column=5).value,
                "sensor_type_name": self.sheet.cell(row=row_num+i, column=6).value
            })

        return result
    
    def get_info(self):

        info_list = []

        for i in range(1, 31):


            group_data = self.get_group_data_by_id(i)

            info_list.append(group_data)

        return {
            "master_station_mac": self.get_master_station_mac(),
            "group_data_list": info_list
        }




if __name__ == "__main__":

    xlsx_methods = XlsxMethods()
    print(xlsx_methods.get_master_station_mac())

    group_data = xlsx_methods.get_group_data_by_id(1)
    # print(group_data)

    info = xlsx_methods.get_info()
    print(info)


    # Output:
    {
        'master_station_mac': '00000000', 
        'group_data_list': [
            {'group_id': 1, 'bracket_number': 7, 'station_mac': '0100', 'sensor_list': [{'sensor_type': 'A', 'sensor_mac': '010A', 'sensor_type_name': '倾角'}, {'sensor_type': 'B', 'sensor_mac': '010B', 'sensor_type_name': '倾角'}, {'sensor_type': 'C', 'sensor_mac': '010C', 'sensor_type_name': '倾角'}, {'sensor_type': 'D', 'sensor_mac': '010D', 'sensor_type_name': '压力'}, {'sensor_type': 'E', 'sensor_mac': '010E', 'sensor_type_name': '压力'}]}, 
            {'group_id': 2, 'bracket_number': 12, 'station_mac': '0200', 'sensor_list': [{'sensor_type': 'A', 'sensor_mac': '020A', 'sensor_type_name': '倾角'}, {'sensor_type': 'B', 'sensor_mac': '020B', 'sensor_type_name': '倾角'}, {'sensor_type': 'C', 'sensor_mac': '020C', 'sensor_type_name': '倾角'}, {'sensor_type': 'D', 'sensor_mac': '020D', 'sensor_type_name': '压力'}, {'sensor_type': 'E', 'sensor_mac': '020E', 'sensor_type_name': '压力'}]}
        ]
    }